package org.prs.persistence;

import com.prs.util.PrsException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.prs.model.Guardian;
import org.prs.persistence.connector.MySQLDatabaseConnector;

public class GuardianDatabasePersistence {

    private Connection conn;
    private ResultSet result;

    public GuardianDatabasePersistence() {
    }

    public boolean insertGuardian(Guardian guardian) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertGuardian = null;
        try {
            insertGuardian = conn.prepareStatement("insert into guardian set"
                    + " childid=?, relationshiptypeid=?, firstname=?, middlename=?,"
                    + " lastname=?, phonenumber=?");
            insertGuardian.setInt(1, guardian.getChildId());
            insertGuardian.setInt(2, guardian.getRelationshipTypeId());
            insertGuardian.setString(3, guardian.getFirstName());
            insertGuardian.setString(4, guardian.getMiddleName());
            insertGuardian.setString(5, guardian.getLastName());
            insertGuardian.setString(6, guardian.getPhoneNumber());
            insertGuardian.executeUpdate();
        } catch (SQLException e) {
            inserted = false;
        }
        return inserted;
    }

    public boolean updateGuardian(Guardian guardian) {
        boolean updated = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement updateGuardian = null;
        try {
            updateGuardian = conn.prepareStatement("update guardian set"
                    + " childid=?, relationshiptypeid=?, firstname=?, middlename=?,"
                    + " lastname=?, phonenumber=? where guardianid=?");
            updateGuardian.setInt(1, guardian.getChildId());
            updateGuardian.setInt(2, guardian.getRelationshipTypeId());
            updateGuardian.setString(3, guardian.getFirstName());
            updateGuardian.setString(4, guardian.getMiddleName());
            updateGuardian.setString(5, guardian.getLastName());
            updateGuardian.setString(6, guardian.getPhoneNumber());
            updateGuardian.setInt(7, guardian.getGuardianId());
            updateGuardian.executeUpdate();
        } catch (SQLException e) {
            updated = false;
        }
        return updated;
    }

    public boolean deleteGuardian(int guardianId) {
        boolean deleted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement deleteGuardian = null;
        try {
            deleteGuardian = conn.prepareStatement("delete from guardian where guardianid=?");
            deleteGuardian.setInt(1, guardianId);
            deleteGuardian.executeUpdate();
        } catch (SQLException e) {
            deleted = false;
        }
        return deleted;
    }

    public List<Guardian> getChildGuardians(int childId) {
        List<Guardian> guardians = new ArrayList<Guardian>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectChildGuardians = null;
        try {
            selectChildGuardians = conn.prepareStatement("select * from guardian where childid=?");
            selectChildGuardians.setInt(1, childId);
            result = selectChildGuardians.executeQuery();
            while (result.next()) {               
                guardians.add(getGuardian(result));
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return guardians;
    }

    public Guardian getGuardianById(int guardianId) {
        Guardian guardian = new Guardian();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectGuardianById = null;
        try {
            selectGuardianById = conn.prepareStatement("select * from guardian where guardianid=?");
            selectGuardianById.setInt(1, guardianId);
            result = selectGuardianById.executeQuery();
            while (result.next()) {
                guardian = getGuardian(result);


            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return guardian;
    }

    public Guardian getGuardian(ResultSet result) {
        Guardian guardian = new Guardian();
        try {
            guardian = new Guardian();
            guardian.setChildId(result.getInt("childid"));
            guardian.setGuardianId(result.getInt("guardianid"));
            guardian.setRelationshipTypeId(result.getInt("relationshiptypeid"));
            guardian.setFirstName(result.getString("firstname"));
            guardian.setLastName(result.getString("lastname"));
            guardian.setMiddleName(result.getString("middlename"));
            guardian.setPhoneNumber(result.getString("phonenumber"));
        } catch (SQLException e) {
            throw  new PrsException(e);
        }
        return guardian;
    }
}
